<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * A SpicaMySQLCallableStatement object provides a way to call stored procedures in MySQL.
 * A stored procedure is stored in a database; the call to the stored procedure is
 * what a SpicaMySQLCallableStatement object contains.
 * This call is written in an escape syntax that may take one of two forms: one form
 * with a result parameter, and the other without one
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: CallableStatement.php 1505 2009-12-21 18:42:59Z pcdinh $
 */

/**
 * Static statement interface
 */
require_once 'library/spica/core/datasource/db/Statement.php';

/**
 * Result set interface
 */
require_once 'library/spica/core/datasource/db/ResultSet.php';

/**
 * Callable interface
 */
require_once 'library/spica/core/datasource/db/CallableStatement.php';

class SpicaMySQLCallableStatement extends SpicaMySQLCommonStatement implements SpicaCallableStatement
{
    /**
     * The SQL query
     *
     * @var string
     */
    protected $_lastQuery;

    /**
     *
     * @var array
     */
    protected $_outParameters = array();

    /**
     * Indicates if the SQL command is a FUNCTION call or SP call
     *
     * @var bool
     */
    protected $_isFunctionCall = false;

    /**
     * Multiple result sets
     *
     * @var array
     */
    protected $_resultSets = array();

    /**
     * Constructs a <code>SpicaMySQLCallableStatement</code> object
     *
     * @param SpicaMySQLConnection $dbConn
     * @param string $sql A query like "CALL get_user(1, @first, @last)"
     */
    public function __construct($dbConn, $sql)
    {
        $this->_dbConn    = $dbConn;
        $this->_lastQuery = trim($sql);

        // FIXME Try to find a better algorithm
        if (0 === stripos($this->_lastQuery, 'call', 0))
        {
            $this->_isFunctionCall = false;
        }
    }

    /**
     * Executes the SQL statement in this SpicaCallableStatement object,
     * which may be a SELECT on function calls or stored procedure call.
     *
     * Because stored procedures or functions can return multiple result sets,
     * this method will return a set of <code>SpicaMySQLResultSet</code>. If a stored
     * procedure causes an error while fetching a result set, an exception will be returned
     * as a replacement. Therefore, it is required to check each object returned from the set.
     *
     * @see    http://bugs.php.net/bug.php?id=42548 N + 1 result sets in SP
     * @throws SpicaDatabaseException
     * @return bool true if the SQL statement can return a result set;
     *              false if the SQL statement does not return a result set (INSERT/DELETE/UPDATE)
     */
    public function execute()
    {
        if (true === $this->_dbConn->isClosed())
        {
            throw new SpicaDatabaseException('The connection to database server is closed. ', null, null, null);
        }

        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection(); // establish connection
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute the SQL command because database connection has been closed. ', null, null, null, $ex);
        }

        // Clean up previous result sets
        $this->_closeAllOpenResults();

        // Do not use mysqli_query. Use mysql_real_query or mysqli_multi_query instead
        // @see http://bugs.php.net/bug.php?id=30645
        $success  = mysqli_real_query($conn, $this->_lastQuery);

        if (false === $success)
        {
            throw new SpicaDatabaseException('Unable to execute the stored procedure. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        do
        {
            // transfers a result set from the last query
            // returns a buffered result object or FALSE if an error occurred or the query didn't return a result set
            $result = mysqli_store_result($conn);

            // returns a buffered result object
            if (false !== $result)
            {
                $readQuery = (false === $result) ? false : true;
                /* @var $result mysqli_result */
                $this->_resultSets[] = new SpicaMySQLResultSet($result, $readQuery, $this->_dbConn->getSQLIdentifierCase());
            }
        } while (mysqli_more_results($conn) && mysqli_next_result($conn));

        // Check error at the end. See http://bugs.php.net/bug.php?id=50483
        if (mysqli_errno($conn) > 0)
        {
            throw new SpicaDatabaseException('The batch queries contain an error that made statement execution not a total success. ', mysqli_error($conn), mysqli_sqlstate($conn), mysqli_errno($conn));
        }

        if (true === $this->_isFunctionCall)
        {
            // Functions can't return result sets
            return false;
        }

        return (count($this->_resultSets) > 0);
    }

    /**
     * Gets result sets returned from SpicaMySQLCallableStatement#execute()
     *
     * @return array
     */
    public function getResultSets()
    {
        return $this->_resultSets;
    }

    /**
     * Add a query into the batch.
     */
    public function addBatch()
    {
        // FIXME
    }

    /**
     * Executes a batch queries.
     *
     * @return array An array of integers
     */
    public function executeBatch()
    {
        // FIXME
    }

    /**
     *
     */
    public function registerOutParameter($name)
    {
        $this->_outParameters[] = $name;
    }

    /**
     * Issues a second query to retrieve all output parameters.
     *
     * @throws SQLException if an error occurs
     */
    private function retrieveOutParams()
    {
        // Cac bien lay t SELECT @@TEN_BIEN
    }

    /**
     * Checks if the statement object is closed
     *
     * @throws SpicaDatabaseException when the statement is closed
     */
    protected function _checkClosed()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query
            because the query statement is closed. ');
        }
    }

    /**
     * Closes all open result set in the batch of queries
     *
     * @see SpicaMySQLStatement::_realClose()
     */
    protected function _closeAllOpenResults()
    {
        if (false !== empty($this->_resultSets))
        {
            foreach ($this->_resultSets as $num => $rs)
            {
                if ($rs instanceof SpicaMySQLResultSet)
                {
                    $rs->close();
                }
            }
        }

        $this->_resultSets = array();
        $this->_previousBatchResultOffset = -1;
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        $this->_closeAllOpenResults();
        $this->_currentResultSet = null;
        $this->_isClosed         = true;

        return true;
    }
}

?>